package com.zygh.policeasset.mapper;

import com.zygh.policeasset.pojo.*;
import com.zygh.policeasset.pojo.vo.ProjectDetailStatus;
import com.zygh.policeasset.pojo.vo.ProjectInfoVO;

import java.util.List;

import com.zygh.policeasset.pojo.vo.ProjectStatisticsCountVO;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

@Mapper
public interface ProjectMapper {
    @Select({"select p.*,c.type as type,c.name as categoryName,pr.status as status,pr.opinion as opinion from project p left join category c on p.category=c.id left join processrecord pr on p.id=pr.project_id and pr.process_name='project' order by p.id desc "})
    @Results({@Result(property = "projectNo", column = "project_no"), @Result(property = "commissionNo", column = "commission_no"), @Result(property = "companyApply", column = "company_apply"), @Result(property = "capitalSource", column = "capital_source"), @Result(property = "projectBase", column = "project_base"), @Result(property = "startDatetime", column = "start_datetime")})
    List<Project> findAll();

    @Select({"select id,project_no,name from project  order by id desc "})
    @Results({@Result(property = "projectNo", column = "project_no")})
    List<Project> findAllProjcetName();

    @Select({"select id,project_no,name from project where id = #{id}"})
    @Results({@Result(property = "projectNo", column = "project_no")})
    Project findAllProjcetNameByid(int i);

    @Update({"update project set name = #{name},project_no = #{projectNo} where id = #{id}"})
    int updateProjcetNameByid(@Param("name") String str, @Param("id") int i, @Param("projectNo") String str2);

    @Delete({"delete from project where id = #{id}"})
    int deleteProjcetNameByid(@Param("id") int i);

    @Select({"select p.*,c.type as type,c.name as categoryName,pr.status as status,pr.opinion as opinion from project p " +
            "left join category c on p.category=c.id " +
            "left join processrecord pr on p.id=pr.project_id and pr.process_name='project' " +
            "where p.pid=0 order by p.id desc "})
    @Results({@Result(property = "projectNo", column = "project_no"), @Result(property = "commissionNo", column = "commission_no"), @Result(property = "companyApply", column = "company_apply"), @Result(property = "capitalSource", column = "capital_source"), @Result(property = "projectBase", column = "project_base"), @Result(property = "startDatetime", column = "start_datetime")})
    List<Project> findAllByRoot();

    @Select({"<script>", "select p.*,c.type as type,c.name as categoryName,af.app_status as appStatus,af.approval_steps_id as approvalStepsId " +
            "from project p left join category c on p.category=c.id " +
            "left join audit_flow af on p.flow_no = af.flow_no " +
            "left join user u on af.applicant = u.id " +
            "<where>p.pid=#{pid} <if test='name != null  '>and p.name like concat('%',#{name},'%') </if>" +
            "<if test='time1 != null and time2 != null and time1.length &gt; 0 and time2.length &gt; 0 '>" +
            "and p.start_datetime between concat(#{time1},' 00:00:00') and  concat(#{time2},' 23:59:59') </if>" +
            "<if test='projectNo != null and projectNo.length &gt; 0'>and p.project_no = #{projectNo} </if>" +
            "<if test='userId != null '> and af.applicant=#{userId} </if>" +
            "<if test='status != null'> and af.app_status = #{status} </if>" +
            "</where>order by p.id desc </script>"})
    @Results({@Result(property = "projectNo", column = "project_no"),
            @Result(property = "commissionNo", column = "commission_no"),
            @Result(property = "companyApply", column = "company_apply"),
            @Result(property = "capitalSource", column = "capital_source"),
            @Result(property = "projectBase", column = "project_base"),
            @Result(property = "startDatetime", column = "start_datetime"),
            @Result(property = "flowNo", column = "flow_no"),
            @Result(property = "retailerId", column = "retailer_id")})
    List<Project> findChildByRoot(@Param("pid") Integer pid,
                                  @Param("status") Integer status,
                                  @Param("name") String name,
                                  @Param("time1") String time1,
                                  @Param("time2") String time2,
                                  @Param("projectNo") String projectNo,
                                  @Param("userId") Integer userId);

    @Select({"select p.*,c.type as type,un.unit_name as retailerName,c.name as categoryName,af.app_status,u.name as userName from project p left join category c on p.category=c.id left join unit un on un.type='cg' and un.company_id = p.retailer_id left join audit_flow af on p.flow_no = af.flow_no left join user u on af.applicant = u.id where p.id=#{id}"})
    @Results({@Result(property = "projectNo", column = "project_no"), @Result(property = "commissionNo", column = "commission_no"), @Result(property = "companyApply", column = "company_apply"), @Result(property = "capitalSource", column = "capital_source"), @Result(property = "projectBase", column = "project_base"), @Result(property = "startDatetime", column = "start_datetime"), @Result(property = "flowNo", column = "flow_no"), @Result(property = "appStatus", column = "app_status"), @Result(property = "retailerId", column = "retailer_id")})
    Project findById(int i);

    @Select({"<script>", "select p.*,c.type as type,c.name as categoryName,pr.status as status,pr.opinion as opinion,IFNULL(SUM(ctp.contractpay_money),0) as sumMoney from project p left join category c on p.category = c.id left join processrecord pr on p.id = pr.project_id and pr.process_name = 'project' left join contract_pay ctp on p.project_no = ctp.contractpay_id where pr.status = #{status} and p.pid != 0 <if test='category != null and category &gt; 0'>and p.category = #{category} </if><if test='name != null and name.length &gt; 0'>and p.name like '%${name}%' </if><if test='startTime != null and startTime.length &gt; 0'>and #{startTime} &lt;= p.start_datetime </if><if test='endTime != null and endTime.length &gt; 0'>and p.start_datetime &lt;= #{endTime} </if>group by p.id order by p.id desc </script>"})
    @Results({@Result(property = "projectNo", column = "project_no"), @Result(property = "commissionNo", column = "commission_no"), @Result(property = "companyApply", column = "company_apply"), @Result(property = "capitalSource", column = "capital_source"), @Result(property = "projectBase", column = "project_base"), @Result(property = "startDatetime", column = "start_datetime"), @Result(property = "sumMoney", column = "sumMoney")})
    List<Project> findByStatus(@Param("category") Integer i, @Param("name") String str, @Param("status") Integer i2, @Param("startTime") String str2, @Param("endTime") String str3);

    @Select({"<script>", "select p.id from project p <where> p.name = #{name} </where></script>"})
    Integer getAdd(Project project);

    @Insert({"insert into project (pid,category,commission_no,project_no,company_apply,name,capital_source,content,budget,project_base,contactor,department,start_datetime,data_labels_id,retailer_id,flow_no) values (#{pid},#{category},#{commissionNo},#{projectNo},#{companyApply},#{name},#{capitalSource},#{content},#{budget},#{projectBase},#{contactor},#{department},#{startDatetime},#{dataLabelsId},#{retailerId},#{flowNo})"})
    @Options(useGeneratedKeys = true, keyProperty = "id", keyColumn = "id")
    Integer add(Project project);

    @Update({"update project set category=#{category},commission_no=#{commissionNo},company_apply=#{companyApply},name=#{name},capital_source=#{capitalSource},content=#{content},budget=#{budget},project_base=#{projectBase},contactor=#{contactor},department=#{department},start_datetime=#{startDatetime},retailer_id=#{retailerId} where id=#{id}"})
    Integer update(Project project);

    @Select({"<script>", "select p.*,c.type as type,c.name as categoryName,pr.status as status,pr.opinion as opinion from project p left join category c on p.category=c.id where p.pid=0  <if test='category != null and category != 0'>and p.category=#{category} </if><if test='name != null'>and p.name like '%${name}%' </if><if test='commissionNo != null and commissionNo.length &gt; 0'>and p.commission_no = #{commissionNo} </if><if test='time1 != null and time2 != null'>and p.start_datetime between #{time1} and #{time2} </if>order by p.id desc </script>"})
    @Results({@Result(property = "projectNo", column = "project_no"), @Result(property = "commissionNo", column = "commission_no"), @Result(property = "companyApply", column = "company_apply"), @Result(property = "capitalSource", column = "capital_source"), @Result(property = "projectBase", column = "project_base"), @Result(property = "startDatetime", column = "start_datetime")})
    List<Project> findSuperiorProject(@Param("category") int i, @Param("status") int i2, @Param("name") String str, @Param("commissionNo") String str2, @Param("time1") String str3, @Param("time2") String str4);

    @Select({"<script>", "select p.*,c.type as type,c.name as categoryName from project p left join category c on p.category=c.id where p.pid=0  <if test='category != null and category != 0'>and p.category=#{category} </if><if test='name != null'>and p.name like '%${name}%' </if><if test='commissionNo != null and commissionNo.length &gt; 0'>and p.commission_no = #{commissionNo} </if><if test='time1 != null and time2 != null'>and p.start_datetime between #{time1} and #{time2} </if>order by p.id desc </script>"})
    @Results({@Result(property = "projectNo", column = "project_no"), @Result(property = "commissionNo", column = "commission_no"), @Result(property = "companyApply", column = "company_apply"), @Result(property = "capitalSource", column = "capital_source"), @Result(property = "projectBase", column = "project_base"), @Result(property = "startDatetime", column = "start_datetime")})
    List<Project> search(@Param("category") int i, @Param("status") int i2, @Param("name") String str, @Param("commissionNo") String str2, @Param("time1") String str3, @Param("time2") String str4, @Param("userId") Integer num);

    @Update({"update project set app_status=#{status} where id=#{id}"})
    Integer updateStatus(int i, int i2);

    @Update({"update project set project_no=#{no} where id=#{id}"})
    Integer updateProjectNo(@Param("id") int i, @Param("no") String str);

    @Delete({"delete from project where id=#{id}"})
    Integer remove(int i);

    @Select({"<script>", "SELECT p.*,c.type,c.name AS categoryName,pr.status,pr.opinion AS opinion,t.section_name,t.id As sectionId,co.company_name,co.id AS company_id FROM ( SELECT fs.project_no,pr.*,fs.`section_id` FROM completed fs LEFT JOIN processrecord pr ON fs.id = pr.project_id WHERE pr.process_name='completed'  AND pr.status = 2 AND fs.asset_new_status = #{status}) pr LEFT JOIN project p ON p.project_no = pr.project_no LEFT JOIN category c ON p.category = c.id LEFT JOIN section t ON pr.`section_id`= t.id LEFT JOIN `contract` ct ON p.project_no = ct.project_no AND ct.section_id=t.id  LEFT JOIN `company` co ON co.id = ct.company_id<where><if test='commissionNo != null and commissionNo.length &gt; 0'>and p.commission_no = #{commissionNo} </if><if test='name != null and name.length &gt; 0'>and p.name like '%${name}%' </if><if test='companyId != null and companyId.length &gt; 0 and companyId != 0' >and ct.company_id  = #{companyId} </if></where>order by p.id desc </script>"})
    @Results({@Result(property = "projectNo", column = "project_no"), @Result(property = "commissionNo", column = "commission_no"), @Result(property = "companyApply", column = "company_apply"), @Result(property = "capitalSource", column = "capital_source"), @Result(property = "projectBase", column = "project_base"), @Result(property = "startDatetime", column = "start_datetime"), @Result(property = "section_name", column = "section_name"), @Result(property = "company_id", column = "company_id"), @Result(property = "sectionId", column = "sectionId")})
    List<Project> findbyCompleted(@Param("companyId") String str, @Param("name") String str2, @Param("status") String str3, @Param("commissionNo") String str4);

    @Select({"<script>", "SELECT p.name,af.approval_steps_id as approvalStepsId,p.project_no,p.company_apply," +
            "p.capital_source,p.budget,p.contactor,p.start_datetime,c.type,c.name AS categoryName," +
            "slt.start_provision_date as startProvisionDate,t.section_name,t.id As sectionId," +
            "co.company_name,co.id AS company_id,slt.certificate_num as certificateNum," +
            "slt.audit_amount,co.company_name as companyName,pab.id as id,af.app_status," +
            "u.name as userName,pab.flow_no FROM project_assets_business pab " +
            "LEFT JOIN settlement slt on pab.settlement_id = slt.id " +
            "LEFT JOIN project p ON p.project_no = slt.project_no " +
            "LEFT JOIN category c ON p.category = c.id " +
            "LEFT JOIN section t ON slt.`section_id`= t.id " +
            "LEFT JOIN `contract` ct ON p.project_no = ct.project_no AND ct.section_id=t.id " +
            "left join audit_flow af on pab.flow_no = af.flow_no " +
            "left join user u on af.applicant = u.id " +
            "LEFT JOIN `company` co ON co.id = ct.company_id " +
            "<where>" +
            "<if test='commissionNo != null and commissionNo.length &gt; 0'>and p.commission_no = #{commissionNo} </if>" +
            "<if test='name != null and name.length &gt; 0'>and p.name like '%${name}%' </if>" +
            "<if test='companyId != null and companyId.length &gt; 0 ' >and ct.company_id  = #{companyId} </if>" +
            "<if test='company != null and company.length &gt; 0' >and ct.company_id  = #{company} </if>" +
            "<if test='dataLabelsId != null ' >and slt.data_labels_id  = #{dataLabelsId} </if>" +
            "<if test='id != null '>   and pab.id = #{id}  </if> " +
            "<if test='userId != null '> and af.applicant=#{userId} </if>" +
            "<if test=' status != null and status.length &gt; 0'> and af.app_status = #{status} </if>" +
            "</where>" +
            "order by slt.id desc " +
            "</script>"})
    @Results({@Result(property = "projectNo", column = "project_no"),
            @Result(property = "commissionNo", column = "commission_no"),
            @Result(property = "companyApply", column = "company_apply"),
            @Result(property = "capitalSource", column = "capital_source"),
            @Result(property = "projectBase", column = "project_base"),
            @Result(property = "startDatetime", column = "start_datetime"),
            @Result(property = "section_name", column = "section_name"),
            @Result(property = "company_id", column = "company_id"),
            @Result(property = "sectionId", column = "sectionId"),
            @Result(property = "auditAmount", column = "audit_amount"),
            @Result(property = "flowNo", column = "flow_no"),
            @Result(property = "appStatus", column = "app_status")})
    List<Project> findbySettlement(@Param("companyId") String companyId,
                                   @Param("name") String name,
                                   @Param("status") String status,
                                   @Param("commissionNo") String commissionNo,
                                   @Param("dataLabelsId") Integer dataLabelsId,
                                   @Param("userId") Integer userId,
                                   @Param("company") String company,
                                   @Param("id") Integer id);

    @Select({"<script>", "SELECT p.name,af.approval_steps_id as approvalStepsId,p.project_no,p.company_apply,p.capital_source,p.budget,p.contactor,p.start_datetime,c.type,c.name AS categoryName,slt.start_provision_date as startProvisionDate,t.section_name,t.id As sectionId,co.company_name,co.id AS company_id,slt.certificate_num as certificateNum,slt.audit_amount,co.company_name as companyName,pab.id as id,af.app_status,u.name as userName,pab.flow_no FROM project_assets_business pab LEFT JOIN settlement slt on pab.settlement_id = slt.id LEFT JOIN project p ON p.project_no = slt.project_no LEFT JOIN category c ON p.category = c.id LEFT JOIN section t ON slt.`section_id`= t.id LEFT JOIN `contract` ct ON p.project_no = ct.project_no AND ct.section_id=t.id left join audit_flow af on pab.flow_no = af.flow_no left join user u on af.applicant = u.id LEFT JOIN `company` co ON co.id = ct.company_id <where><if test='id != null '>   and pab.id = #{id}  </if> </where></script>"})
    @Results({@Result(property = "projectNo", column = "project_no"), @Result(property = "commissionNo", column = "commission_no"), @Result(property = "companyApply", column = "company_apply"), @Result(property = "capitalSource", column = "capital_source"), @Result(property = "projectBase", column = "project_base"), @Result(property = "startDatetime", column = "start_datetime"), @Result(property = "section_name", column = "section_name"), @Result(property = "company_id", column = "company_id"), @Result(property = "sectionId", column = "sectionId"), @Result(property = "auditAmount", column = "audit_amount"), @Result(property = "flowNo", column = "flow_no"), @Result(property = "appStatus", column = "app_status")})
    Project findbySettlementById(@Param("id") Integer num);

    @Select({"<script>", "SELECT COUNT(*) FROM ( SELECT fs.project_no,pr.*,fs.`section_id` FROM final_statement fs LEFT JOIN processrecord pr ON fs.id = pr.project_id WHERE pr.process_name='final_statement'  AND pr.status = 2 AND fs.asset_new_status = #{status}) pr LEFT JOIN project p ON p.project_no = pr.project_no LEFT JOIN category c ON p.category = c.id LEFT JOIN section t ON pr.`section_id`= t.id LEFT JOIN `contract` ct ON p.project_no = ct.project_no AND ct.section_id=t.id  LEFT JOIN `company` co ON co.id = ct.company_id<where><if test='commissionNo != null and commissionNo.length &gt; 0'>and p.commission_no = #{commissionNo} </if><if test='name != null and name.length &gt; 0'>and p.name like '%${name}%' </if><if test='companyId != null and companyId.length &gt; 0 and companyId != 0' >and ct.company_id  = #{companyId} </if></where>order by p.id desc </script>"})
    int findCount(@Param("companyId") String str, @Param("status") String str2);

    @Update({"update project set payment = 1 where id=#{id}"})
    Integer affirmPay(int i);

    @Select({"<script>", "SELECT p.*,c.type,c.name AS categoryName FROM project p LEFT JOIN category c ON p.category = c.id <where>and p.payment = 1 <if test='name != null and name.length &gt; 0'>and p.name like '%${name}%' </if><if test='commissionNo != null and commissionNo.length &gt; 0'>and p.commission_no = #{commissionNo} </if></where>GROUP BY p.id order by p.id desc </script>"})
    @Results({@Result(property = "projectNo", column = "project_no"), @Result(property = "commissionNo", column = "commission_no"), @Result(property = "companyApply", column = "company_apply"), @Result(property = "capitalSource", column = "capital_source"), @Result(property = "projectBase", column = "project_base"), @Result(property = "startDatetime", column = "start_datetime")})
    List<Project> getPay(@Param("name") String str, @Param("commissionNo") String str2);

    @Results({@Result(property = "projectNo", column = "project_no"), @Result(property = "commissionNo", column = "commission_no"), @Result(property = "companyApply", column = "company_apply"), @Result(property = "capitalSource", column = "capital_source"), @Result(property = "projectBase", column = "project_base"), @Result(property = "startDatetime", column = "start_datetime")})
    @Select({"select * from project where project_no = #{projectNo} "})
    Project findByNo(@Param("projectNo") String str);

    @Select({"select name from project where project_no = #{projectNo} and pid != 0"})
    String findByProjectNo(@Param("projectNo") String str);

    @Select({"select *  from project"})
    List<Project> selectAll();

    @Select({"select p.name,p.project_no  from project p left join audit_flow a on p.flow_no=a.flow_no where a.app_status = 2 and pid !=0"})
    @Results({@Result(property = "projectNo", column = "project_no"), @Result(property = "projectName", column = "name")})
    List<ProjectNameType> selectBytype();

    @Select({"select p.* from project p left join category c on p.category = c.id left join audit_flow af on p.flow_no = af.flow_no where pid !=0 and category = #{id} and af.app_status =2"})
    @Results({@Result(property = "projectNo", column = "project_no"), @Result(property = "commissionNo", column = "commission_no"), @Result(property = "companyApply", column = "company_apply"), @Result(property = "capitalSource", column = "capital_source"), @Result(property = "projectBase", column = "project_base"), @Result(property = "startDatetime", column = "start_datetime")})
    List<Project> toExcel(Integer num);

    @Update({"update project set start_provision_date = #{startProvisionDate}, certificate_num = #{certificateNum} where project_no=#{projectNo}"})
    Integer updateSPD(@Param("projectNo") String str, @Param("startProvisionDate") String str2, @Param("certificateNum") String str3);

    @Update({"update project set asset_new_status = #{assetApprovalStatus} where project_no=#{projectNo} "})
    Integer updateCgAssetApprovalStatus(@Param("projectNo") String str, @Param("assetApprovalStatus") Integer num);

    @Select({"select count(*) from project where asset_new_status in (1,2)  and project_no=#{projectNo} "})
    Integer check(@Param("projectNo") String str);

    @Select({"<script>", "select p.id,p.project_no as buildNum,p.name as projectName,p.retailer_id as companyId  from project p  <where>  p.pid != 0 <if test='name != null and name.length &gt; 0'>and p.name like concat('%',#{name} ,'%') </if><if test='buildNum != null and buildNum.length &gt; 0'>and p.project_no = #{buildNum} </if><if test='category != null'>and p.category = #{category} </if><if test='unitId != null'>and p.retailer_id = #{unitId} </if></where> order by p.id desc </script>"})
    List<ProjectInfoVO> getProjectInfo(@Param("name") String str, @Param("buildNum") String str2, @Param("sectionId") Integer num, @Param("category") Integer num2, @Param("unitId") Integer num3);

    @Select({"<script>" +
            "select p.*,c.type as type,c.name as categoryName from project p " +
            "left join category c on p.category=c.id " +
            "left join audit_flow af on p.flow_no = af.flow_no " +
            "<where>" +
            "<if test='projectNo != null and projectNo.length &gt; 0'>and p.project_no = #{projectNo} </if>" +
            "<if test='sectionId != null'>and ct.section_id = #{sectionId} </if>" +
            "<if test='type != null and type.length &gt; 0'>and c.type = #{type} </if>" +
            "and af.app_status = 2 and p.category !=4 </where>order by p.id desc " +
            "</script>"})
    @Results({@Result(property = "projectNo", column = "project_no"), @Result(property = "commissionNo", column = "commission_no"), @Result(property = "companyApply", column = "company_apply"), @Result(property = "capitalSource", column = "capital_source"), @Result(property = "projectBase", column = "project_base"), @Result(property = "startDatetime", column = "start_datetime")})
    List<Project> selectProject(@Param("projectNo") String str, @Param("sectionId") Integer num, @Param("type") String str2);

    @Select({"SELECT\n" +
            "\tcount( * ) as projectStatus,\n" +
            "\tp.id,\n" +
            "\t'project' AS type \n" +
            "FROM\n" +
            "\tproject p\n" +
            "\tLEFT JOIN audit_flow af ON p.flow_no = af.flow_no \n" +
            "WHERE\n" +
            "\tp.project_no = #{projectNo} \n" +
            "\tAND af.app_status = 2 \n" +
            "\tAND p.category != 4 UNION ALL\n" +
            "SELECT\n" +
            "\tcount( * ) as projectStatus,\n" +
            "\tt.id,\n" +
            "\t'tender' AS type \n" +
            "FROM\n" +
            "\ttender t\n" +
            "\tLEFT JOIN section s ON t.id = s.tender_id\n" +
            "\tLEFT JOIN audit_flow af ON t.flow_no = af.flow_no \n" +
            "WHERE\n" +
            "\tt.project_no = #{projectNo} \n" +
            "\tAND af.app_status = 2 \n" +
            "\tAND s.id = #{sectionId} UNION ALL\n" +
            "SELECT\n" +
            "\tcount( * ) as projectStatus,\n" +
            "\tc.id,\n" +
            "\t'contract' AS type \n" +
            "FROM\n" +
            "\tcontract c\n" +
            "\tLEFT JOIN audit_flow af ON c.flow_no = af.flow_no \n" +
            "WHERE\n" +
            "\tc.project_no = #{projectNo} \n" +
            "\tAND af.app_status = 2 \n" +
            "\tAND c.section_id = #{sectionId} UNION ALL\n" +
            "SELECT\n" +
            "\tcount( * ) as projectStatus,\n" +
            "\tcd.id,\n" +
            "\t'completed' AS type \n" +
            "FROM\n" +
            "\tcompleted cd\n" +
            "\tLEFT JOIN audit_flow af ON cd.flow_no = af.flow_no \n" +
            "WHERE\n" +
            "\tcd.project_no = #{projectNo} \n" +
            "\tAND af.app_status = 2 \n" +
            "\tAND cd.section_id = #{sectionId} UNION ALL\n" +
            "SELECT\n" +
            "\tcount( * ) as projectStatus,\n" +
            "\tse.id,\n" +
            "\t'settlement' AS type \n" +
            "FROM\n" +
            "\tsettlement se\n" +
            "\tLEFT JOIN audit_flow af ON se.flow_no = af.flow_no \n" +
            "WHERE\n" +
            "\tse.project_no = #{projectNo} \n" +
            "\tAND af.app_status = 2 \n" +
            "\tAND se.section_id = #{sectionId} UNION ALL\n" +
            "SELECT\n" +
            "\tcount( * ) as projectStatus,\n" +
            "\tfs.id,\n" +
            "\t'finalStatement' AS type \n" +
            "FROM\n" +
            "\tfinal_statement fs\n" +
            "\tLEFT JOIN audit_flow af ON fs.flow_no = af.flow_no \n" +
            "WHERE\n" +
            "\tfs.project_no = #{projectNo} \n" +
            "\tAND af.app_status = 2 \n" +
            "\tAND fs.section_id = #{sectionId} "})
    List<ProjectDetailStatus> selectProjectDetailStatus(@Param("projectNo") String projectNo,
                                                        @Param("sectionId") Integer sectionId);

    @Select({"SELECT  '立项管理' as name,count( * ) as numberSum  FROM  project p left join audit_flow af on p.flow_no = af.flow_no  WHERE  category != 4   and p.start_datetime between #{StartTime} and #{endTime} UNION ALL SELECT  '招标管理' as name,count( * ) as numberSum  FROM  tender t left join audit_flow af on t.flow_no = af.flow_no  WHERE\n  t.start_datetime between #{StartTime} and #{endTime} UNION ALL SELECT  '合同管理' as name,count( * ) as numberSum  FROM  contract c left join audit_flow af on c.flow_no = af.flow_no  WHERE  c.start_time between #{StartTime} and #{endTime} UNION ALL SELECT  '竣工管理' as name,count( * ) as numberSum  FROM  completed cd left join audit_flow af on cd.flow_no = af.flow_no  WHERE  cd.final_time between #{StartTime} and #{endTime} UNION ALL SELECT  '结算审计管理' as name,count( * ) as numberSum  FROM  settlement se left join audit_flow af on se.flow_no = af.flow_no  WHERE  se.audit_time between #{StartTime} and #{endTime}  UNION ALL SELECT  '决算审计管理' as name,count( * ) as numberSum  FROM  final_statement fs left join audit_flow af on fs.flow_no = af.flow_no   WHERE   fs.create_time between #{StartTime} and #{endTime} "})
    List<AssetsTypeSum> lifeCycle(@Param("StartTime") String str, @Param("endTime") String str2);

    @Results({@Result(property = "projectNo", column = "project_no")})
    @Select({"select * from project where flow_no  = #{flowNo} and category = 4"})
    Project selectIdByFlowNo(String str);

    @Select({"<script>" +
            "SELECT  '1' AS sort, IF ( count( * ) > 0, 1, 0 ) AS STATUS,p.id,'project' as type FROM project p " +
            "LEFT JOIN audit_flow af ON p.flow_no = af.flow_no  " +
            "WHERE  p.project_no = #{projectNo} and p.pid !=4 " +
            "AND af.app_status = 2 " +
            "<if test='sectionId != null'>UNION ALL " +
            "SELECT '2' AS sort,IF( count( * ) > 0, 1, 0 ) AS STATUS,t.id,'tender' as type  FROM tender t " +
            "LEFT JOIN audit_flow af ON t.flow_no = af.flow_no  " +
            "WHERE t.project_no = #{projectNo} AND af.app_status = 2 UNION ALL " +
            "SELECT '3' AS sort,IF( count( * ) > 0, 1, 0 ) AS STATUS,c.id,'contract' as type  FROM contract c LEFT JOIN audit_flow af ON c.flow_no = af.flow_no  " +
            "WHERE c.project_no = #{projectNo} AND c.section_id = #{sectionId} AND af.app_status = 2 UNION ALL " +
            "SELECT '4' AS sort, IF( count( * ) > 0, 1, 0 ) AS STATUS,cd.id,'completed' as type  FROM completed cd LEFT JOIN audit_flow af ON cd.flow_no = af.flow_no  WHERE cd.project_no = #{projectNo} AND cd.section_id = #{sectionId} AND af.app_status = 2 UNION ALL " +
            "SELECT '5' AS sort, IF( count( * ) > 0, 1, 0 ) AS STATUS,s.id,'settlement' as type  FROM settlement s LEFT JOIN audit_flow af ON s.flow_no = af.flow_no  WHERE s.project_no = #{projectNo} AND s.section_id = #{sectionId} AND af.app_status = 2 UNION ALL " +
            "SELECT '6' AS sort, IF( count( * ) > 0, 1, 0 ) AS STATUS,pab.id,'project_assets_business' as type  FROM project_assets_business pab LEFT JOIN settlement s ON pab.settlement_id = s.id LEFT JOIN audit_flow af ON pab.flow_no = af.flow_no  WHERE s.project_no = #{projectNo} AND s.section_id = #{sectionId} AND af.app_status =2 </if></script>"})
    List<BusinessProcessStatus> findBusinessProcess(@Param("projectNo") String str,
                                                    @Param("sectionId") Integer num);

    @Select({"SELECT\n\t'1' AS sort,\nIF\n\t( count( * ) > 0, 1, 0 ) AS STATUS,p.id,'project' as type \nFROM\n\tproject p\n\tLEFT JOIN audit_flow af ON p.flow_no = af.flow_no \nWHERE\n\tp.project_no = #{projectNo} \n\t\n\tAND af.app_status = 2 UNION ALL\nSELECT\n\t'2' AS sort,\nIF\n\t( count( * ) > 0, 1, 0 ) AS STATUS,pab.id,'purchase_assets_business' as type \nFROM\n\tpurchase_assets_business pab\n\tLEFT JOIN project p ON pab.project_id = p.id\n\tLEFT JOIN audit_flow af ON pab.flow_no = af.flow_no \nWHERE\n\tp.project_no = #{projectNo}\n\t\n\tAND af.app_status = 2 "})
    List<BusinessProcessStatus> findBusinessProcessPurchase(@Param("projectNo") String str);

    @Select({"select *  from project where pid != 0"})
    List<Project> doTest();

    @Select({"select id,retailer_id as userId from project  where category =4 AND pid != 0 and asset_new_status in (0,3)  "})
    List<Project> cgassets();

    @Select({"<script>", "select p.project_no as buildNum,p.name as projectName from project p  " +
            "left join category c on p.category=c.id " +
            "<where>  pid != 0 and category != 4 " +
            "<if test='name != null and name.length &gt; 0'>and  p.name like concat('%',#{name} ,'%') </if>" +
            "<if test='type != null and type.length &gt; 0'>and c.type = #{type} </if>" +
            "</where> order by p.id desc </script>"})
    List<ProjectInfoVO> getProjectByName(@Param("name") String str, @Param("type") String str2);


    @Select({"<script>" +
            "SELECT\n" +
            "\tcount( * ) AS count,\n" +
            "\t'project' AS type,\n" +
            "<if test='status == null '>  -1  AS status </if>" +
            "<if test='status != null '> af.app_status AS status </if>" +
            "FROM\n" +
            "\tproject p\n" +
            "\tLEFT JOIN audit_flow af ON p.flow_no = af.flow_no \n" +
            "<where>\n" +
            "<if test='status != null '> af.app_status = #{status} </if>" +
            "<if test='time1 != null and time1.length &gt; 0 and time2 != null and time2.length &gt; 0'> and p.start_datetime between #{time1} and #{time2} </if>" +
            "\tAND p.category != 4 UNION ALL\n" +
            "</where>" +
            "SELECT\n" +
            "\tcount( * ) AS count,\n" +
            "\t'tender' AS type,\n" +
            "<if test='status == null '>  -1  AS status </if>" +
            "<if test='status != null '> af.app_status AS status </if>" +
            "FROM\n" +
            "\ttender t\n" +
            "\tLEFT JOIN section s ON t.id = s.tender_id\n" +
            "\tLEFT JOIN audit_flow af ON t.flow_no = af.flow_no \n" +
            "<where>\n" +
            "<if test='status != null '> af.app_status = #{status} </if>" +
            "<if test='time1 != null and time1.length &gt; 0 and time2 != null and time2.length &gt; 0'> and t.start_datetime between #{time1} and #{time2} </if>" +
            "</where>" +
            "UNION ALL\n" +
            "SELECT\n" +
            "\tcount( * ) AS count,\n" +
            "\t'contract' AS type,\n" +
            "<if test='status == null '>  -1  AS status </if>" +
            "<if test='status != null '> af.app_status AS status </if>" +
            "FROM\n" +
            "\tcontract c\n" +
            "\tLEFT JOIN audit_flow af ON c.flow_no = af.flow_no \n" +
            "<where>\n" +
            "<if test='status != null '> af.app_status = #{status} </if>" +
            "<if test='time1 != null and time1.length &gt; 0 and time2 != null and time2.length &gt; 0'> and c.confirm_time between #{time1} and #{time2} </if>" +
            "</where>" +
            "UNION ALL\n" +
            "SELECT\n" +
            "\tcount( * ) AS count,\n" +
            "\t'completed' AS type,\n" +
            "<if test='status == null '>  -1  AS status </if>" +
            "<if test='status != null '> af.app_status AS status </if>" +
            "FROM\n" +
            "\tcompleted cd\n" +
            "\tLEFT JOIN audit_flow af ON cd.flow_no = af.flow_no \n" +
            "<where>\n" +
            "<if test='status != null '> af.app_status = #{status} </if>" +
            "<if test='time1 != null and time1.length &gt; 0 and time2 != null and time2.length &gt; 0'> and cd.final_time between #{time1} and #{time2} </if>" +
            "</where>" +
            "UNION ALL\n" +
            "SELECT\n" +
            "\tcount( * ) AS count,\n" +
            "\t'settlement' AS type,\n" +
            "<if test='status == null '>  -1  AS status </if>" +
            "<if test='status != null '> af.app_status AS status </if>" +
            "FROM\n" +
            "\tsettlement se\n" +
            "\tLEFT JOIN audit_flow af ON se.flow_no = af.flow_no \n" +
            "<where>\n" +
            "<if test='status != null '> af.app_status = #{status} </if>" +
            "<if test='time1 != null and time1.length &gt; 0 and time2 != null and time2.length &gt; 0'> and se.audit_time between #{time1} and #{time2} </if>" +
            "</where>" +
            "UNION ALL\n" +
            "SELECT\n" +
            "\tcount( * ) AS count,\n" +
            "\t'finalStatement' AS type,\n" +
            "<if test='status == null '>  -1  AS status </if>" +
            "<if test='status != null '> af.app_status AS status </if>" +
            "FROM\n" +
            "\tfinal_statement fs\n" +
            "\tLEFT JOIN audit_flow af ON fs.flow_no = af.flow_no \n" +
            "<where>\n" +
            "<if test='status != null '> af.app_status = #{status} </if>" +
            "<if test='time1 != null and time1.length &gt; 0 and time2 != null and time2.length &gt; 0'> and fs.audit_time between #{time1} and #{time2} </if>" +
            "</where>" +
            "</script>"})
    List<ProjectStatisticsCountVO> projectStatisticsCount(@Param("status") Integer status,
                                                          @Param("time1") String time1,
                                                          @Param("time2") String time2);

    @Select({"select p.id from project p left join category c on p.category = c.id where c.type = #{type} and p.pid = 0"})
    Integer findPidByCategory(String type);

    @Select({"select fa.* from file_attachment fa left join project p on  " +
            "  p.id = fa.id  where p.project_no = #{projectNo} " +
            "and p.pid !=0 and p.category = 4 and fa.project_name = 'purchase' " +
            "and fa.file_type = 1 "})
    List<UploadFile> queryPurchaseByProjectNo(String projectNo);

    @Select({"select fa.* from file_attachment fa " +
            "left join project_assets_business pab on pab.id = project_id.project_id " +
            "left join settlement s on pab.settlement_id = s.id " +
            "  where s.project_no = #{projectNo} and section_id = #{sectionId} and fa.project_name = 'project' and fa.file_type = 1 "})
    List<UploadFile> queryProjectByProjectNo(@Param("projectNo") String projectNo,
                                             @Param("sectionId") Integer sectionId);
}